package com.meetingshow.dao.meeting;

import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONArray;

import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.rowset.SqlRowSet;

public class MeetingDAO extends JdbcDaoSupport implements IMeetingDAO {
	private String sql = null; // "UPDATE T_USER_INTEREST(USERNAME, MEETING_ID,

	// COMMENTS) values(?,?,?)";

	/**
	 * 设置某个用户关注的会议 attention标志位：true ---设置关注 or false---取消关注
	 * param格式为：[{"username":"jiao","meeting_id":"1111","comments":"for
	 * test","isAttention":"true"},{...},.....] 返回值：成功返回true，失败返回false
	 * URL: http://localhost:8080/MeetingShow/meeting/meetingOp.do?method=setAttentionList&param=aJsonObject
	 */
	public int setAttention(String meetingList) throws Exception {
		try {
			JSONArray ja = JSONArray.fromObject(meetingList);
			String username = ja.getJSONObject(0).getString("username");
			String meeting_id = ja.getJSONObject(0).getString("meeting_id");
			String comments = ja.getJSONObject(0).getString("comments");
			Boolean isAttention = ja.getJSONObject(0).getBoolean("isAttention");
			
			int rtn = 0;
			
			if (isAttention) {
				sql = "INSERT INTO T_USER_INTEREST(USERNAME, MEETING_ID, COMMENTS) values(?,?,?)";
				rtn = getJdbcTemplate().update(sql,
						new Object[] { username, meeting_id, comments },
						new int[] { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR });
			}
			else{
				sql = "DELETE FROM T_USER_INTEREST WHERE USERNAME = ? AND MEETING_ID = ? ";
				rtn = getJdbcTemplate().update(sql,
						new Object[] { username, meeting_id },
						new int[] { Types.VARCHAR, Types.VARCHAR});
			}
			
			return rtn;
		} catch (Exception e) {
			// ServiceFactory.getLogManager().error(arg0, arg1);
			e.printStackTrace();
			throw e;
		}
	}

	/**
	 * 查询并返回用户的关注列表 param格式为：username
	 * 返回值为json数组：[{"id":"111","name":"第一届卫生会议","name_en":"first health
	 * meeting",....},{...},.....]
	 * URL: http://localhost:8080/MeetingShow/meeting/meetingOp.do?method=getAttentionList&param=username
	 */
	public JSONArray getAttentionList(String username) throws Exception {
		System.out.println("invoke of getAttentionList start ..............");
		try {
			sql = "SELECT * FROM T_USER_INTEREST WHERE USERNAME = '" + username
					+ "'";
			System.out.println("getAttentionList sql is " + sql);
			List list = getJdbcTemplate().queryForList(sql);
			JSONArray ja = JSONArray.fromObject(list);
			System.out.println("getAttentionList sql result is "
					+ ja.toString());
			return ja;
		} catch (Exception e) {
			// ServiceFactory.getLogManager().error(arg0, arg1);
			e.printStackTrace();
			throw e;
		}
	}

	/**
	 * 按类别返回推荐列表，为用户推荐当前热门或者较为有价值的会议 
	 * 本方法返回按照type排名前3位的会议 
	 * 参数：type的值为popular, level, value 之一
	 * 返回值为json数组：[{"id":"111","name":"第一届卫生会议","name_en":"first health meeting",....},{...},.....]
	 * URL: http://localhost:8080/MeetingShow/meeting/meetingOp.do?method=getTopListByType&param=type
	 */
	public JSONArray getTopListByType(String type) throws Exception {
		try {
			sql = "SELECT * FROM T_MEETING ORDER BY " + type
					+ " DESC LIMIT 0,50";
			System.out.println("getTopListByType sql is :" + sql);
			List list = getJdbcTemplate().queryForList(sql);
			JSONArray ja = JSONArray.fromObject(list);
			System.out
					.println("getTopListByTypegetTopListByTypegetTopListByType:"
							+ ja.toString());
			return ja;
		} catch (Exception e) {
			// ServiceFactory.getLogManager().error(arg0, arg1);
			e.printStackTrace();
			throw e;
		}
	}

	/**
	 * 根据标题查询感兴趣的会议 
	 * 参数：查询关键字title
	 * 返回值为json数组：[{"id":"111","name":"第一届卫生会议","name_en":"first health meeting",....},{...},.....]
	 * URL: http://localhost:8080/MeetingShow/meeting/meetingOp.do?method=searchByTitle&param=title
	 */
	public JSONArray searchByTitle(String title) throws Exception {
		try {
			sql = "SELECT * FROM T_MEETING WHERE NAME LIKE '%" + title + "%'  LIMIT 0,50";
			List list = getJdbcTemplate().queryForList(sql);
			JSONArray ja = JSONArray.fromObject(list);
			return ja;
		} catch (Exception e) {
			// ServiceFactory.getLogManager().error(arg0, arg1);
			e.printStackTrace();
			throw e;
		}
	}

	/**
	 * 按照行业，城市或者日期查找并返回会议列表，并排序， 参数：sort为industry, city, date之一
	 * 返回值为json数组：[{"id":"111","name":"第一届卫生会议","name_en":"first health meeting",....},{...},.....]
	 * URL: http://localhost:8080/MeetingShow/meeting/meetingOp.do?method=getMeetingListBySort&param=sort
	 */
	public JSONArray getMeetingListBySort(String sort) throws Exception {
		try {
			sql = "SELECT * FROM T_MEETING GROUP BY '" + sort
					+ "' ORDER BY START_DATE";
			List list = getJdbcTemplate().queryForList(sql);
			JSONArray ja = JSONArray.fromObject(list);
			return ja;
		} catch (Exception e) {
			// ServiceFactory.getLogManager().error(arg0, arg1);
			e.printStackTrace();
			throw e;
		}
	}

	/**
	 * 添加会议
	 * 此方法暂未用到
	 */
	public int addMeeting(String name, String name_en, String start_date,
			String end_date, String type, String district, String location,
			String abstracts, String industry, String organizer,
			String sponsor, String co_organizer, String contact,
			String contact_phone, String contact_fax, String email,
			String website, String charge, int popular, int level, int value)
			throws Exception {
		sql = "INSERT INTO T_MEETING(NAME, NAME_EN, START_DATE, END_DATE, TYPE,"
				+ "DISTRICT, LOCATION, ABSTRACT, INDUSTRY, ORGANIZER, "
				+ "SPONSOR, CO_ORGANIZER, CONTACT, CONTACT_PHONE, CONTACT_FAX, "
				+ "EMAIL, WEBSITE, CHARGE, POPULAR, LEVEL, "
				+ "VALUE "
				+ ") values(?,?,?,?,?,"
				+ "?,?,?,?,?,"
				+ "?,?,?,?,?,"
				+ "?,?,?,?,?," 
				+ "?)";
		try {
			int rtn = getJdbcTemplate().update(
					sql,
					new Object[] { name, name_en, start_date, end_date, type,
							district, location, abstracts, industry, organizer,
							sponsor, co_organizer, contact, contact_phone,
							contact_fax, email, website, charge, popular,
							level, value },
					new int[] { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
							Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
							Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
							Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
							Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
							Types.VARCHAR, Types.VARCHAR, Types.DOUBLE,
							Types.INTEGER, Types.INTEGER, Types.INTEGER });
			return rtn;
		} catch (Exception e) {
			// ServiceFactory.getLogManager().error(arg0, arg1);
			throw e;
		}
	}

	/**
	 * 会议查询
	 * 此方法暂未用到
	 */
	public List getMeetingList(String name, String name_en, String start_date,
			String end_date, String type, String district, String industry,
			String organizer, String sponsor, String co_organizer, int popular,
			int level, int value) throws Exception {
		try {
			sql = "SELECT * FROM T_MEETING  " + 
			"where 1=1 ";
			if (name !=null && name.length() >0)
				sql += " and name like '%"+ name +"%' ";
			if (name_en !=null && name_en.length() >0)
				sql += " and name_en like '%"+ name_en +"%' ";
			if (start_date !=null && start_date.length() >0)
				sql += "and start_date >= '%"+ start_date +"%' ";
			 if (end_date !=null && end_date.length() >0)
				sql += "and end_date <= '%"+ end_date +"%' ";
			if (type !=null && type.length() >0)
				sql += "and type like '%"+ type +"%' ";
			if (district !=null && district.length() >0)
				sql += "and district like '%"+ district +"%' ";
			if (industry !=null && industry.length() >0)
				sql += "and industry like '%"+ industry +"%' ";
			if (organizer !=null && organizer.length() >0)
				sql += "and organizer like '%"+ organizer +"%' ";
			if (sponsor !=null && sponsor.length() >0)
				sql += "and sponsor like '%"+ sponsor +"%' ";
			if (co_organizer !=null && co_organizer.length() >0)
				sql += "and co_organizer like '%"+ co_organizer +"%' ";
			
			if (popular > 0)
				sql += "popular = '%"+ popular +"%' ";
			if (level > 0)
				sql += "level = '%"+ level +"%' ";
			if (value  >0)
				sql += "value = '%"+ name_en +"%' ";
			System.out.println("MeetingMg SQL is : " + sql);
			List meetingList = getJdbcTemplate().queryForList(sql);
			System.out.println("sssssssssssssssssssssss:" + meetingList.toString());
			return meetingList;
		} catch (Exception e) {
			// ServiceFactory.getLogManager().error(arg0, arg1);
			throw e;
		}
	}
	
	/**
	 * 更新会议
	 * 此方法暂未用到
	 */
	public int updateMeeting(String name, String name_en, String start_date,
			String end_date, String type, String district, String location,
			String abstracts, String industry, String organizer,
			String sponsor, String co_organizer, String contact,
			String contact_phone, String contact_fax, String email,
			String website, String charge, int popular, int level, int value, int id)
			throws Exception {
		System.out.println("updateMeeting sql start...");
		
		sql = "UPDATE T_MEETING SET NAME='"+name+"', NAME_EN='"+name_en+"', START_DATE='"+start_date+"', END_DATE='"+end_date+"', TYPE='"+type+"',"
				+ "DISTRICT='"+district+"', LOCATION='"+location+"', ABSTRACT='"+abstracts+"', INDUSTRY='"+industry+"', ORGANIZER='"+organizer+"', "
				+ "SPONSOR='"+sponsor+"', CO_ORGANIZER='"+co_organizer+"', CONTACT='"+contact+"', CONTACT_PHONE='"+contact_phone+"', CONTACT_FAX='"+contact_fax+"', "
				+ "EMAIL='"+email+"', WEBSITE='"+website+"', CHARGE="+charge+", POPULAR="+popular+", LEVEL="+level+", VALUE="+value+" "
				+ "WHERE ID="+id+" ";
		try {
			getJdbcTemplate().execute(sql);
			System.out.println("updateMeeting sql finished..." + sql);
			return 0;
		} catch (Exception e) {
			// ServiceFactory.getLogManager().error(arg0, arg1);
			throw e;
		}
	}
	
	/**
	 * 删除会议
	 * 此方法暂未用到
	 */
	public int delMeeting(int id)
			throws Exception {
		sql = "DELETE FROM T_MEETING "
				+ "WHERE ID=? ";
		try {
			int rtn = getJdbcTemplate().update(
					sql,
					new Object[] { id },
					new int[] { Types.INTEGER});
			return rtn;
		} catch (Exception e) {
			// ServiceFactory.getLogManager().error(arg0, arg1);
			throw e;
		}
	}
}